SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION [Live].[tvfGetPropReason]
(
	-- Add the parameters for the function here
	@ExportId INT
	
)
RETURNS TABLE
AS
RETURN 
(
	-- Declare the return variable here
	--DECLARE @ReturnVal VARCHAR(150)
	--DECLARE @AssetType VARCHAR(50)

--	SELECT @AssetType = (SELECT a.AssetType FROM FSFundModel.Reporting.Asset a WHERE a.ExportId = @ExportId AND a.AssetIntrnlId = @AssetIntrnlId)
	-- Add the T-SQL statements to compute the return value here
	--SELECT 	@ReturnVal = CASE WHEN @AssetType = 'Instrument' THEN acts.[NBSReason] ELSE 'Proprietary' END	
		
		
		SELECT [a].[AssetIntrnlId],  CASE WHEN MAX(a.AssetType)  = 'Instrument' THEN MAX(acts.[NBSReason]) ELSE 'Proprietary' END	 AS  NBSReason
			, max (acts.PortfolioPurchase) as PortfolioPurchase	

	
	 FROM  FSFundModel.Reporting.Asset a 
	INNER JOIN  FSFundModel.Reporting.Instrument i 
	ON a.InstrumentId =i.InstrumentId 
	AND i.ExportId = a.ExportId 
	LEFT JOIN  FSFundModel.Reporting.Trade t 
	ON t.ExportId = a.ExportId 
	AND t.InstrumentId = a.InstrumentId
	AND t.PortfolioId = a.PortfolioId
	AND t.LotId = a.LotId

	LEFT JOIN  FSFundModel.Reporting.ActivitySummary acts 
	ON acts.ExportId = a.ExportId 
	AND acts.InstrumentId = a.InstrumentId 
	AND acts.ActivitySummaryId= t.ActivitySummaryId
	
	
	WHERE a.ExportId = @ExportId
	--*AND a.AssetIntrnlId= @AssetIntrnlId
	--AND acts.ActivityType = 'Purchase'
	
	--SET @ReturnVal = CASE WHEN @AssetType <> 'Instrument' THEN 'Proprietary' ELSE @ReturnVal END	
	GROUP BY [a].[AssetIntrnlId]
	)
	-- Return the result of the function
	--RETURN @ReturnVal

--END		
GO
